<?php
/**
 * @Author: ChinaYie
 * @Date:   2018-04-11 09:47:54
 */
class MySQL {
    private $db_host;
    private $db_port;
    private $db_user;
    private $db_pwd;
    private $database;
    private $charset;

    public $pdo;
    private $quotes;

    //执行sql语句
    public $sqlString;



    /**
     * 初始化数据库 并连接数据库
     * 为兼容以前的代码使用，所有方法都没变
     */
    public function __construct($db_host,$db_user, $db_pwd, $db_name,$db_port=3306,$db_charset = 'utf8') {
        $this->db_host = $db_host;
        $this->db_user = $db_user;
        $this->db_pwd = $db_pwd;
        $this->database = $db_name;
        $this->db_port = $db_port;
        $this->charset = $db_charset;
        $this->quotes = get_magic_quotes_gpc();
        $this->pdo = new PDO("mysql:host=$this->db_host;port=$this->db_port;dbname=$this->database","$this->db_user","$this->db_pwd");
        $this->sqlString = "SET NAMES '{$this->charset}'";
        $this->pdo->exec($this->sqlString);
    }

    /**
     * @param $dbname
     * 重新选择数据库
     */
    public function setDatabase($dbname){
        if($this->database != $dbname && is_resource($this->pdo))
            $this->pdo->query("use $dbname");
    }

    /**
     * 查询单行数据
     * @param $table
     * @param $col
     * @param $condition
     * @return mixed
     */
    public function getRow($table,$col = '*',$condition = 1){
        $col = is_array($col) ? implode(',',$col) : $col;
        $this->sqlString = "select ".$col." from ".$table." where ".$condition;
        $res = $this->pdo->query($this->sqlString);
        return $res->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * 查询多行数据
     * @param $table
     * @param $col
     * @param $condition
     * @return array
     */
    public function getRows($table,$col = '*',$condition = 1){
        $col = is_array($col) ? implode(',',$col) : $col;

        $this->sqlString = "select ".$col." from ".$table." where ".$condition;

        $res = $this->pdo->query( $this->sqlString);
        return $res->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 查询数量
     * @param $table
     * @param $col
     * @param $condition
     * @return array
     */
    public function getCounts($table,$condition = 1){
        $this->sqlString = "select count(*) from " . $table . " where " . $condition;
        $res = $this->pdo->query( $this->sqlString);
        $counts = $res->fetchAll(PDO::FETCH_ASSOC);
        return $counts[0]['count(*)'];
    }

    /**
     * 执行sql语句 返回受影响行数
     * @param $sql
     * @return int
     */
    public function exec($sql) {
        $this->sqlString = $sql;
        return $this->pdo->exec($this->sqlString);
    }


    /**
     * 执行sql语句 返回结果集合
     * @param $sql
     * @return array
     */
    public function query($sql) {
        $this->sqlString = $sql;
        $res = $this->pdo->query($this->sqlString);
        return $res->fetchAll(PDO::FETCH_ASSOC);
    }


    /**
     * 插入数据
     * @param $table
     * @param $valuesArray
     * @return bool|string
     */
    public function add($table, $valuesArray) {

        $val = $this->toSetString($valuesArray);
        $this->sqlString = "INSERT LOW_PRIORITY INTO `$table` SET $val";
        if($this->pdo->query($this->sqlString)){
            $id = $this->pdo->lastInsertId();
            return $id;
        }else{
            return false;
        }
    }

    /**
     * 更新数据
     * @param $table
     * @param $values
     * @param $where
     * @return bool
     */
    public function update($table, $values, $where) {

        $val = $this->toSetString($values);
        $this->sqlString = "UPDATE LOW_PRIORITY `$table` SET $val WHERE $where";
        //error_log($sql, 3, 'sql_print.txt');
        return $this->pdo->exec($this->sqlString);
    }

    /**
     * 删除数据
     * @param $table
     * @param $where
     * @return bool
     */
    public function delete($table, $where) {

        $this->sqlString = "DELETE FROM `$table` WHERE $where";

        if($this->pdo->query($this->sqlString)){
            return true;
        }else{
            return false;
        }
    }

    public function __destruct() {
        if (is_resource($this->pdo)) {
            $this->pdo=null;
        }
    }

    /**
     * 开启事务
     * @return bool
     */
    public function begin() {
        return $this->pdo->beginTransaction();
    }

    /**
     * 事务回滚
     * @return bool
     */
    public function rollback() {
        return $this->pdo->rollback();
    }

    /**
     * 事务提交
     * @return bool
     */
    public function commit() {
        return $this->pdo->commit();
    }


    /**
     * 事务提交
     * @return bool
     */
    public function getSql() {
        return $this->sqlString;
    }

    /**
     * 数据转换
     * @param $dataArray
     * @return string
     */
    private function toSetString($dataArray)
    {
        $result = "";
        if (!is_array($dataArray))
            return $result;

        foreach($dataArray as $key => $value)
        {
            if ( $result != "" )
            {
                $result .= ", ";
            }

            if ( $value == 'sysdate()' )
            {
                $result .= $key ." = ". $this->escapeString($value);
            }
            else
            {
                $result .= $key ." = '". $this->escapeString(($value)) ."'";
            }
        }

        return $result;
    }

    /**
     * 输入过滤
     * @param $input
     * @return string
     */
    private function escapeString($input)
    {
        return stripslashes($input);
    }


}